{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {
    "collapsed": false
   },
   "source": [
    "## Datawhale组队学习Pandas\n",
    "## 第六章 连接\n",
    "## 第六次打卡"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "collapsed": false
   },
   "source": [
    "![](https://ai-studio-static-online.cdn.bcebos.com/59e953ef71674273a54c6b987f980d4130c31ef6a98b4542853273029a7f28ef)\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Looking in indexes: https://mirror.baidu.com/pypi/simple/\n",
      "Collecting pandas==1.1.5\n",
      "\u001b[?25l  Downloading https://mirror.baidu.com/pypi/packages/fd/70/e8eee0cbddf926bf51958c7d6a86bc69167c300fa2ba8e592330a2377d1b/pandas-1.1.5-cp37-cp37m-manylinux1_x86_64.whl (9.5MB)\n",
      "\u001b[K     |████████████████████████████████| 9.5MB 24.2MB/s eta 0:00:01\n",
      "\u001b[?25hRequirement already satisfied: pytz>=2017.2 in /opt/conda/envs/python35-paddle120-env/lib/python3.7/site-packages (from pandas==1.1.5) (2019.3)\n",
      "Requirement already satisfied: python-dateutil>=2.7.3 in /opt/conda/envs/python35-paddle120-env/lib/python3.7/site-packages (from pandas==1.1.5) (2.8.0)\n",
      "Requirement already satisfied: numpy>=1.15.4 in /opt/conda/envs/python35-paddle120-env/lib/python3.7/site-packages (from pandas==1.1.5) (1.16.4)\n",
      "Requirement already satisfied: six>=1.5 in /opt/conda/envs/python35-paddle120-env/lib/python3.7/site-packages (from python-dateutil>=2.7.3->pandas==1.1.5) (1.15.0)\n",
      "Installing collected packages: pandas\n",
      "  Found existing installation: pandas 0.23.4\n",
      "    Uninstalling pandas-0.23.4:\n",
      "      Successfully uninstalled pandas-0.23.4\n",
      "Successfully installed pandas-1.1.5\n"
     ]
    }
   ],
   "source": [
    "!pip install pandas==1.1.5 \r\n",
    "!unzip data/data65130/data.zip  -d data/ > /dev/null 2>&1"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "import numpy as np\n",
    "import pandas as pd"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "collapsed": false
   },
   "source": [
    "## 一、关系型连接\n",
    "### 1. 连接的基本概念\n",
    "\n",
    "把两张相关的表按照某一个或某一组键连接起来是一种常见操作，例如学生期末考试各个科目的成绩表按照$\\color{red}{姓名}$和$\\color{red}{班级}$连接成总的成绩表，又例如对企业员工的各类信息表按照$\\color{red}{员工ID号}$进行连接汇总。由此可以看出，在关系型连接中，$\\color{red}{键}$是十分重要的，往往用`on`参数表示。\n",
    "\n",
    "另一个重要的要素是连接的形式。在`pandas`中的关系型连接函数`merge`和`join`中提供了`how`参数来代表连接形式，分为左连接`left`、右连接`right`、内连接`inner`、外连接`outer`，它们的区别可以用如下示意图表示：\n",
    "\n",
    "![](https://ai-studio-static-online.cdn.bcebos.com/4f74c7f5034b4860830532b0243daa21d9108a3629e7489198ff160eaebbe05b)\n",
    "\n",
    "\n",
    "从图中可以看到，所谓左连接即以左边的键为准，如果右边表中的键于左边存在，那么就添加到左边，否则则处理为缺失值，右连接类似处理。内连接只负责合并两边同时出现的键，而外连接则会在内连接的基础上包含只在左边出现以及只在右边出现的值，因此外连接又叫全连接。\n",
    "\n",
    "上面这个简单的例子中，同一个表中的键没有出现重复的情况，那么如果出现重复的键应该如何处理？只需把握一个原则，即只要两边同时出现的值，就以笛卡尔积的方式加入，如果单边出现则根据连接形式进行处理。其中，关于笛卡尔积可用如下例子说明：设左表中键`张三`出现两次，右表中的`张三`也出现两次，那么逐个进行匹配，最后产生的表必然包含`2*2`个姓名为`张三`的行。下面是一个对应例子的示意图：\n",
    "\n",
    "![](https://ai-studio-static-online.cdn.bcebos.com/105bd79bde0f4758824f3719c3eccbfc9b324a2db53a49299c49e29d30f28df8)\n",
    "\n",
    "\n",
    "显然在不同的场合应该使用不同的连接形式。其中左连接和右连接是等价的，由于它们的结果中的键是被一侧的表确定的，因此常常用于有方向性地添加到目标表。内外连接两侧的表，经常是地位类似的，想取出键的交集或者并集，具体的操作还需要业务的需求来判断。\n",
    "\n",
    "### 2. 值连接\n",
    "\n",
    "在上面示意图中的例子中，两张表根据某一列的值来连接，事实上还可以通过几列值的组合进行连接，这种基于值的连接在`pandas`中可以由`merge`函数实现，例如第一张图的左连接："
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Name</th>\n",
       "      <th>Age</th>\n",
       "      <th>Gender</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>San Zhang</td>\n",
       "      <td>20</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Si Li</td>\n",
       "      <td>30</td>\n",
       "      <td>F</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "        Name  Age Gender\n",
       "0  San Zhang   20    NaN\n",
       "1      Si Li   30      F"
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df1 = pd.DataFrame({'Name':['San Zhang','Si Li'], 'Age':[20,30]})\n",
    "df2 = pd.DataFrame({'Name':['Si Li','Wu Wang'], 'Gender':['F','M']})\n",
    "df1.merge(df2, on='Name', how='left')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "collapsed": false
   },
   "source": [
    "如果两个表中想要连接的列不具备相同的列名，可以通过`left_on`和`right_on`指定："
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>df1_name</th>\n",
       "      <th>Age</th>\n",
       "      <th>df2_name</th>\n",
       "      <th>Gender</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>San Zhang</td>\n",
       "      <td>20</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Si Li</td>\n",
       "      <td>30</td>\n",
       "      <td>Si Li</td>\n",
       "      <td>F</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "    df1_name  Age df2_name Gender\n",
       "0  San Zhang   20      NaN    NaN\n",
       "1      Si Li   30    Si Li      F"
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df1 = pd.DataFrame({'df1_name':['San Zhang','Si Li'], 'Age':[20,30]})\n",
    "df2 = pd.DataFrame({'df2_name':['Si Li','Wu Wang'], 'Gender':['F','M']})\n",
    "df1.merge(df2, left_on='df1_name', right_on='df2_name', how='left')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "collapsed": false
   },
   "source": [
    "如果两个表中的列出现了重复的列名，那么可以通过`suffixes`参数指定。例如合并考试成绩的时候，第一个表记录了语文成绩，第二个是数学成绩："
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Name</th>\n",
       "      <th>Grade_Chinese</th>\n",
       "      <th>Grade_Math</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>San Zhang</td>\n",
       "      <td>70</td>\n",
       "      <td>80</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "        Name  Grade_Chinese  Grade_Math\n",
       "0  San Zhang             70          80"
      ]
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df1 = pd.DataFrame({'Name':['San Zhang'],'Grade':[70]})\n",
    "df2 = pd.DataFrame({'Name':['San Zhang'],'Grade':[80]})\n",
    "df1.merge(df2, on='Name', how='left', suffixes=['_Chinese','_Math'])"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "collapsed": false
   },
   "source": [
    "在某些时候出现重复元素是麻烦的，例如两位同学来自不同的班级，但是姓名相同，这种时候就要指定`on`参数为多个列使得正确连接："
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "        Name  Age Class\n",
      "0  San Zhang   20   one\n",
      "1  San Zhang   21   two\n",
      "__________________________________________\n",
      "        Name Gender Class\n",
      "0  San Zhang      F   two\n",
      "1  San Zhang      M   one\n"
     ]
    }
   ],
   "source": [
    "df1 = pd.DataFrame({'Name':['San Zhang', 'San Zhang'],\n",
    "                    'Age':[20, 21],\n",
    "                    'Class':['one', 'two']})\n",
    "df2 = pd.DataFrame({'Name':['San Zhang', 'San Zhang'],\n",
    "                    'Gender':['F', 'M'],\n",
    "                    'Class':['two', 'one']})\n",
    "print(df1)\n",
    "print(\"__________________________________________\")\n",
    "print(df2)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Name</th>\n",
       "      <th>Age</th>\n",
       "      <th>Class_x</th>\n",
       "      <th>Gender</th>\n",
       "      <th>Class_y</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>San Zhang</td>\n",
       "      <td>20</td>\n",
       "      <td>one</td>\n",
       "      <td>F</td>\n",
       "      <td>two</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>San Zhang</td>\n",
       "      <td>20</td>\n",
       "      <td>one</td>\n",
       "      <td>M</td>\n",
       "      <td>one</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>San Zhang</td>\n",
       "      <td>21</td>\n",
       "      <td>two</td>\n",
       "      <td>F</td>\n",
       "      <td>two</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>San Zhang</td>\n",
       "      <td>21</td>\n",
       "      <td>two</td>\n",
       "      <td>M</td>\n",
       "      <td>one</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "        Name  Age Class_x Gender Class_y\n",
       "0  San Zhang   20     one      F     two\n",
       "1  San Zhang   20     one      M     one\n",
       "2  San Zhang   21     two      F     two\n",
       "3  San Zhang   21     two      M     one"
      ]
     },
     "execution_count": 17,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df1.merge(df2, on='Name', how='left') # 错误的结果\r\n",
    "#只连接了姓名"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Name</th>\n",
       "      <th>Age</th>\n",
       "      <th>Class</th>\n",
       "      <th>Gender</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>San Zhang</td>\n",
       "      <td>20</td>\n",
       "      <td>one</td>\n",
       "      <td>M</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>San Zhang</td>\n",
       "      <td>21</td>\n",
       "      <td>two</td>\n",
       "      <td>F</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "        Name  Age Class Gender\n",
       "0  San Zhang   20   one      M\n",
       "1  San Zhang   21   two      F"
      ]
     },
     "execution_count": 18,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df1.merge(df2, on=['Name', 'Class'], how='left') # 正确的结果"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "collapsed": false
   },
   "source": [
    "查看一下merge`df1.merge?`的其他用法\n",
    "\n",
    "输入输出：\n",
    "```\n",
    "df1.merge(\n",
    "    right,\n",
    "    how='inner',\n",
    "    on=None,\n",
    "    left_on=None,\n",
    "    right_on=None,\n",
    "    left_index=False,\n",
    "    right_index=False,\n",
    "    sort=False,\n",
    "    suffixes=('_x', '_y'),\n",
    "    copy=True,\n",
    "    indicator=False,\n",
    "    validate=None,\n",
    ") -> 'DataFrame'\n",
    "Returns\n",
    "-------\n",
    "DataFrame\n",
    "    A DataFrame of the two merged objects.\n",
    "```\n",
    "描述：\n",
    "```\n",
    "Docstring:\n",
    "Merge DataFrame or named Series objects with a database-style join.\n",
    "如果列对列的合并，index会被忽略\n",
    "The join is done on columns or indexes. If joining columns on\n",
    "columns, the DataFrame indexes *will be ignored*. Otherwise if joining indexes\n",
    "on indexes or indexes on a column or columns, the index will be passed on.\n",
    "```\n",
    "\n",
    "使用方法：\n",
    "```\n",
    "Parameters\n",
    "----------\n",
    "right : DataFrame or named Series\n",
    "    Object to merge with.\n",
    "how : {'left', 'right', 'outer', 'inner'}, default 'inner'\n",
    "    Type of merge to be performed.\n",
    "这里也对四种连接方式进行了说明\n",
    "    * left: use only keys from left frame, similar to a SQL left outer join;\n",
    "      preserve key order.\n",
    "    * right: use only keys from right frame, similar to a SQL right outer join;\n",
    "      preserve key order.\n",
    "    * outer: use union of keys from both frames, similar to a SQL full outer\n",
    "      join; sort keys lexicographically.\n",
    "    * inner: use intersection of keys from both frames, similar to a SQL inner\n",
    "      join; preserve the order of the left keys.\n",
    "on : label or list 两个表中都需要有共有的行或者列名。\n",
    "    Column or index level names to join on. These must be found in both\n",
    "    DataFrames. If `on` is None and not merging on indexes then this defaults\n",
    "    to the intersection of the columns in both DataFrames.\n",
    "left_on : label or list, or array-like 向左合并的标签，也可以是和左边DF一样长的\n",
    "    Column or index level names to join on in the left DataFrame. Can also\n",
    "    be an array or list of arrays of the length of the left DataFrame.\n",
    "    These arrays are treated as if they are columns.\n",
    "right_on : label or list, or array-like 和向左的一样\n",
    "    Column or index level names to join on in the right DataFrame. Can also\n",
    "    be an array or list of arrays of the length of the right DataFrame.\n",
    "    These arrays are treated as if they are columns.\n",
    "left_index : bool, default False 使用左边的DF作为合并时的key\n",
    "    Use the index from the left DataFrame as the join key(s). If it is a\n",
    "    MultiIndex, the number of keys in the other DataFrame (either the index\n",
    "    or a number of columns) must match the number of levels.\n",
    "right_index : bool, default False 使用右边的DF作为合并时的key\n",
    "    Use the index from the right DataFrame as the join key. Same caveats as\n",
    "    left_index.\n",
    "sort : bool, default False \n",
    "lexicographically adv.按字典顺序的\n",
    "    Sort the join keys lexicographically in the result DataFrame. If False,\n",
    "    the order of the join keys depends on the join type (how keyword).\n",
    "suffixes : list-like, default is (\"_x\", \"_y\")后缀，一个两位长度(?)的东西，用于分别在左边和右边的列名后面增加后缀里的内容。\n",
    "    A length-2 sequence where each element is optionally a string\n",
    "    indicating the suffix to add to overlapping column names in\n",
    "    `left` and `right` respectively. Pass a value of `None` instead\n",
    "    of a string to indicate that the column name from `left` or\n",
    "    `right` should be left as-is, with no suffix. At least one of the\n",
    "    values must not be None.\n",
    "copy : bool, default True 是否用副本\n",
    "    If False, avoid copy if possible.\n",
    "indicator : bool or str, default False\n",
    "功能是对输出的Df增加一列，关于每一行合并的来源\n",
    "    If True, adds a column to the output DataFrame called \"_merge\" with\n",
    "    information on the source of each row. The column can be given a different\n",
    "    name by providing a string argument. The column will have a Categorical\n",
    "    type with the value of \"left_only\" for observations whose merge key only\n",
    "    appears in the left DataFrame, \"right_only\" for observations\n",
    "    whose merge key only appears in the right DataFrame, and \"both\"\n",
    "    if the observation's merge key is found in both DataFrames.\n",
    "\n",
    "validate : str, optional，一对一到多对多\n",
    "    If specified, checks if merge is of specified type.\n",
    "\n",
    "    * \"one_to_one\" or \"1:1\": check if merge keys are unique in both\n",
    "      left and right datasets.\n",
    "    * \"one_to_many\" or \"1:m\": check if merge keys are unique in left\n",
    "      dataset.\n",
    "    * \"many_to_one\" or \"m:1\": check if merge keys are unique in right\n",
    "      dataset.\n",
    "    * \"many_to_many\" or \"m:m\": allowed, but does not result in checks.\n",
    "\n",
    "```\n",
    "一些例子\n",
    "```\n",
    "Examples\n",
    "--------\n",
    ">>> df1 = pd.DataFrame({'lkey': ['foo', 'bar', 'baz', 'foo'],\n",
    "...                     'value': [1, 2, 3, 5]})\n",
    ">>> df2 = pd.DataFrame({'rkey': ['foo', 'bar', 'baz', 'foo'],\n",
    "...                     'value': [5, 6, 7, 8]})\n",
    ">>> df1\n",
    "    lkey value\n",
    "0   foo      1\n",
    "1   bar      2\n",
    "2   baz      3\n",
    "3   foo      5\n",
    ">>> df2\n",
    "    rkey value\n",
    "0   foo      5\n",
    "1   bar      6\n",
    "2   baz      7\n",
    "3   foo      8\n",
    "\n",
    "Merge df1 and df2 on the lkey and rkey columns. The value columns have\n",
    "the default suffixes, _x and _y, appended.\n",
    "默认添加了_x和_y的后缀\n",
    "\n",
    ">>> df1.merge(df2, left_on='lkey', right_on='rkey')\n",
    "  lkey  value_x rkey  value_y\n",
    "0  foo        1  foo        5\n",
    "1  foo        1  foo        8\n",
    "2  foo        5  foo        5\n",
    "3  foo        5  foo        8\n",
    "4  bar        2  bar        6\n",
    "5  baz        3  baz        7\n",
    "\n",
    "Merge DataFrames df1 and df2 with specified left and right suffixes\n",
    "appended to any overlapping columns.\n",
    "添加了自定义的后缀\n",
    "\n",
    ">>> df1.merge(df2, left_on='lkey', right_on='rkey',\n",
    "...           suffixes=('_left', '_right'))\n",
    "  lkey  value_left rkey  value_right\n",
    "0  foo           1  foo            5\n",
    "1  foo           1  foo            8\n",
    "2  foo           5  foo            5\n",
    "3  foo           5  foo            8\n",
    "4  bar           2  bar            6\n",
    "5  baz           3  baz            7\n",
    "\n",
    "Merge DataFrames df1 and df2, but raise an exception if the DataFrames have\n",
    "any overlapping columns.\n",
    "\n",
    ">>> df1.merge(df2, left_on='lkey', right_on='rkey', suffixes=(False, False))\n",
    "Traceback (most recent call last):\n",
    "...\n",
    "ValueError: columns overlap but no suffix specified:\n",
    "    Index(['value'], dtype='object')\n",
    "```"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "collapsed": false
   },
   "source": [
    "从上面的例子来看，在进行基于唯一性的连接下，如果键不是唯一的，那么结果就会产生问题。举例中的行数很少，但如果实际数据中有几十万到上百万行的进行合并时，如果想要保证唯一性，除了用`duplicated`检查是否重复外，`merge`中也提供了`validate`参数来检查连接的唯一性模式。这里共有三种模式，即一对一连接`1:1`，一对多连接`1:m`，多对一连接`m:1`连接，第一个是指左右表的键都是唯一的，后面两个分别指左表键唯一和右表键唯一。\n",
    "\n",
    "#### 【练一练】\n",
    "上面以多列为键的例子中，错误写法显然是一种多对多连接，而正确写法是一对一连接，请修改原表，使得以多列为键的正确写法能够通过`validate='1:m'`的检验，但不能通过`validate='m:1'`的检验。\n",
    "\n",
    "通过`validate='1:m'`的检验：左边的表键值唯一  \n",
    "不能通过`validate='m:1'`的检验：右边的表键值不唯一\n",
    "\n",
    "因此把原本的数据修改一下，左边表格不变，右边表格班级改成一样的"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "        Name  Age Class\n",
      "0  San Zhang   20   one\n",
      "1  San Zhang   21   two\n",
      "__________________________________________\n",
      "        Name Gender Class\n",
      "0  San Zhang      F   one\n",
      "1  San Zhang      M   one\n"
     ]
    }
   ],
   "source": [
    "df1 = pd.DataFrame({'Name':['San Zhang', 'San Zhang'],\r\n",
    "                    'Age':[20, 21],\r\n",
    "                    'Class':['one', 'two']})\r\n",
    "df2 = pd.DataFrame({'Name':['San Zhang', 'San Zhang'],\r\n",
    "                    'Gender':['F', 'M'],\r\n",
    "                    'Class':['one', 'one']})\r\n",
    "print(df1)\r\n",
    "print(\"__________________________________________\")\r\n",
    "print(df2)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Name</th>\n",
       "      <th>Age</th>\n",
       "      <th>Class</th>\n",
       "      <th>Gender</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>San Zhang</td>\n",
       "      <td>20</td>\n",
       "      <td>one</td>\n",
       "      <td>F</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>San Zhang</td>\n",
       "      <td>20</td>\n",
       "      <td>one</td>\n",
       "      <td>M</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "        Name  Age Class Gender\n",
       "0  San Zhang   20   one      F\n",
       "1  San Zhang   20   one      M"
      ]
     },
     "execution_count": 23,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#一对多没有报错\r\n",
    "df1.merge(df2, on=['Name', 'Class'],validate='1:m')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 25,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "ename": "MergeError",
     "evalue": "Merge keys are not unique in right dataset; not a many-to-one merge",
     "output_type": "error",
     "traceback": [
      "\u001b[0;31m---------------------------------------------------------------------------\u001b[0m",
      "\u001b[0;31mMergeError\u001b[0m                                Traceback (most recent call last)",
      "\u001b[0;32m<ipython-input-25-ee52e6fb50e4>\u001b[0m in \u001b[0;36m<module>\u001b[0;34m\u001b[0m\n\u001b[1;32m      1\u001b[0m \u001b[0;31m#多对一报错\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0;32m----> 2\u001b[0;31m \u001b[0mdf1\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mmerge\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mdf2\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mon\u001b[0m\u001b[0;34m=\u001b[0m\u001b[0;34m[\u001b[0m\u001b[0;34m'Name'\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0;34m'Class'\u001b[0m\u001b[0;34m]\u001b[0m\u001b[0;34m,\u001b[0m\u001b[0mvalidate\u001b[0m\u001b[0;34m=\u001b[0m\u001b[0;34m'm:1'\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0m",
      "\u001b[0;32m/opt/conda/envs/python35-paddle120-env/lib/python3.7/site-packages/pandas/core/frame.py\u001b[0m in \u001b[0;36mmerge\u001b[0;34m(self, right, how, on, left_on, right_on, left_index, right_index, sort, suffixes, copy, indicator, validate)\u001b[0m\n\u001b[1;32m   7961\u001b[0m             \u001b[0mcopy\u001b[0m\u001b[0;34m=\u001b[0m\u001b[0mcopy\u001b[0m\u001b[0;34m,\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m   7962\u001b[0m             \u001b[0mindicator\u001b[0m\u001b[0;34m=\u001b[0m\u001b[0mindicator\u001b[0m\u001b[0;34m,\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0;32m-> 7963\u001b[0;31m             \u001b[0mvalidate\u001b[0m\u001b[0;34m=\u001b[0m\u001b[0mvalidate\u001b[0m\u001b[0;34m,\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0m\u001b[1;32m   7964\u001b[0m         )\n\u001b[1;32m   7965\u001b[0m \u001b[0;34m\u001b[0m\u001b[0m\n",
      "\u001b[0;32m/opt/conda/envs/python35-paddle120-env/lib/python3.7/site-packages/pandas/core/reshape/merge.py\u001b[0m in \u001b[0;36mmerge\u001b[0;34m(left, right, how, on, left_on, right_on, left_index, right_index, sort, suffixes, copy, indicator, validate)\u001b[0m\n\u001b[1;32m     85\u001b[0m         \u001b[0mcopy\u001b[0m\u001b[0;34m=\u001b[0m\u001b[0mcopy\u001b[0m\u001b[0;34m,\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m     86\u001b[0m         \u001b[0mindicator\u001b[0m\u001b[0;34m=\u001b[0m\u001b[0mindicator\u001b[0m\u001b[0;34m,\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0;32m---> 87\u001b[0;31m         \u001b[0mvalidate\u001b[0m\u001b[0;34m=\u001b[0m\u001b[0mvalidate\u001b[0m\u001b[0;34m,\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0m\u001b[1;32m     88\u001b[0m     )\n\u001b[1;32m     89\u001b[0m     \u001b[0;32mreturn\u001b[0m \u001b[0mop\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mget_result\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n",
      "\u001b[0;32m/opt/conda/envs/python35-paddle120-env/lib/python3.7/site-packages/pandas/core/reshape/merge.py\u001b[0m in \u001b[0;36m__init__\u001b[0;34m(self, left, right, how, on, left_on, right_on, axis, left_index, right_index, sort, suffixes, copy, indicator, validate)\u001b[0m\n\u001b[1;32m    660\u001b[0m         \u001b[0;31m# are in fact unique.\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m    661\u001b[0m         \u001b[0;32mif\u001b[0m \u001b[0mvalidate\u001b[0m \u001b[0;32mis\u001b[0m \u001b[0;32mnot\u001b[0m \u001b[0;32mNone\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0;32m--> 662\u001b[0;31m             \u001b[0mself\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0m_validate\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mvalidate\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0m\u001b[1;32m    663\u001b[0m \u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m    664\u001b[0m     \u001b[0;32mdef\u001b[0m \u001b[0mget_result\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mself\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n",
      "\u001b[0;32m/opt/conda/envs/python35-paddle120-env/lib/python3.7/site-packages/pandas/core/reshape/merge.py\u001b[0m in \u001b[0;36m_validate\u001b[0;34m(self, validate)\u001b[0m\n\u001b[1;32m   1293\u001b[0m             \u001b[0;32mif\u001b[0m \u001b[0;32mnot\u001b[0m \u001b[0mright_unique\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m   1294\u001b[0m                 raise MergeError(\n\u001b[0;32m-> 1295\u001b[0;31m                     \u001b[0;34m\"Merge keys are not unique in right dataset; \"\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0m\u001b[1;32m   1296\u001b[0m                     \u001b[0;34m\"not a many-to-one merge\"\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m   1297\u001b[0m                 )\n",
      "\u001b[0;31mMergeError\u001b[0m: Merge keys are not unique in right dataset; not a many-to-one merge"
     ]
    }
   ],
   "source": [
    "#多对一报错\r\n",
    "df1.merge(df2, on=['Name', 'Class'],validate='m:1')\r\n",
    "#Merge keys are not unique in right dataset; not a many-to-one merge"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "collapsed": false
   },
   "source": [
    "\n",
    "#### 【END】\n",
    "\n",
    "### 3. 索引连接\n",
    "\n",
    "所谓索引连接，就是把索引当作键，因此这和值连接本质上没有区别，`pandas`中利用`join`函数来处理索引连接，它的参数选择要少于`merge`，除了必须的`on`和`how`之外，可以对重复的列指定左右后缀`lsuffix`和`rsuffix`。其中，`on`参数指索引名，单层索引时省略参数表示按照当前索引连接。"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 31,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "           Age\n",
      "Name          \n",
      "San Zhang   20\n",
      "Si Li       30\n",
      "_______________________________________\n",
      "        Gender\n",
      "Name          \n",
      "Si Li        F\n",
      "Wu Wang      M\n",
      "________________________________________\n",
      "           Age Gender\n",
      "Name                 \n",
      "San Zhang   20    NaN\n",
      "Si Li       30      F\n",
      "________________________________________\n",
      "          Age Gender\n",
      "Name                \n",
      "Si Li    30.0      F\n",
      "Wu Wang   NaN      M\n"
     ]
    }
   ],
   "source": [
    "#这个例子里面有一项重复项，左边优先，使得右边独有的就没有了\n",
    "df1 = pd.DataFrame({'Age':[20,30]}, index=pd.Series(['San Zhang','Si Li'],name='Name'))\n",
    "df2 = pd.DataFrame({'Gender':['F','M']}, index=pd.Series(['Si Li','Wu Wang'],name='Name'))\n",
    "print(df1)\n",
    "print(\"_______________________________________\")\n",
    "print(df2)\n",
    "print(\"________________________________________\")\n",
    "print(df1.join(df2, how='left'))\n",
    "print(\"________________________________________\")\n",
    "print(df1.join(df2, how='right'))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "collapsed": false
   },
   "source": [
    "仿照第2小节的例子，写出语文和数学分数合并的`join`版本："
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 33,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "           Grade\n",
      "Name            \n",
      "San Zhang     70\n",
      "________________________________________\n",
      "           Grade\n",
      "Name            \n",
      "San Zhang     80\n",
      "________________________________________\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Name</th>\n",
       "      <th>Grade_Chinese</th>\n",
       "      <th>Grade_Math</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>San Zhang</td>\n",
       "      <td>70</td>\n",
       "      <td>80</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "        Name  Grade_Chinese  Grade_Math\n",
       "0  San Zhang             70          80"
      ]
     },
     "execution_count": 33,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df1 = pd.DataFrame({'Grade':[70]}, index=pd.Series(['San Zhang'], name='Name'))\n",
    "df2 = pd.DataFrame({'Grade':[80]}, index=pd.Series(['San Zhang'], name='Name'))\n",
    "print(df1)\n",
    "print(\"________________________________________\")\n",
    "print(df2)\n",
    "print(\"________________________________________\")\n",
    "df1.join(df2, how='left', lsuffix='_Chinese', rsuffix='_Math').reset_index()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "collapsed": false
   },
   "source": [
    "如果想要进行类似于`merge`中以多列为键的操作的时候，`join`需要使用多级索引，例如在`merge`中的最后一个例子可以如下写出："
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 35,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "                 Age\n",
      "Name      Class     \n",
      "San Zhang one     20\n",
      "          two     21\n",
      "________________________________________\n",
      "                Gender\n",
      "Name      Class       \n",
      "San Zhang two        F\n",
      "          one        M\n"
     ]
    }
   ],
   "source": [
    "df1 = pd.DataFrame({'Age':[20,21]}, index=pd.MultiIndex.from_arrays([['San Zhang', 'San Zhang'],['one', 'two']], names=('Name','Class')))\n",
    "df2 = pd.DataFrame({'Gender':['F', 'M']}, index=pd.MultiIndex.from_arrays([['San Zhang', 'San Zhang'],['two', 'one']], names=('Name','Class')))\n",
    "print(df1)\n",
    "print(\"________________________________________\")\n",
    "print(df2)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th>Age</th>\n",
       "      <th>Gender</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Name</th>\n",
       "      <th>Class</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th rowspan=\"2\" valign=\"top\">San Zhang</th>\n",
       "      <th>one</th>\n",
       "      <td>20</td>\n",
       "      <td>M</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>two</th>\n",
       "      <td>21</td>\n",
       "      <td>F</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                 Age Gender\n",
       "Name      Class            \n",
       "San Zhang one     20      M\n",
       "          two     21      F"
      ]
     },
     "execution_count": null,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df1.join(df2)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "collapsed": false
   },
   "source": [
    "看一看join中有没有什么漏掉的`df1.join?`\n",
    "\n",
    "输入输出与功能：\n",
    "```\n",
    "Signature: df1.join(other, on=None, how='left', lsuffix='', rsuffix='', sort=False) -> 'DataFrame'\n",
    "Returns\n",
    "-------\n",
    "DataFrame\n",
    "    A dataframe containing columns from both the caller and `other`.\n",
    "\n",
    "Docstring:\n",
    "Join columns of another DataFrame.\n",
    "\n",
    "Join columns with `other` DataFrame either on index or on a key\n",
    "column. Efficiently join multiple DataFrame objects by index at once by\n",
    "passing a list.\n",
    "\n",
    "通过传递list高效的结合DF\n",
    "```\n",
    "功能：\n",
    "```\n",
    "Parameters\n",
    "----------\n",
    "可以传入DF组成的list\n",
    "other : DataFrame, Series, or list of DataFrame\n",
    "    Index should be similar to one of the columns in this one. If a\n",
    "    Series is passed, its name attribute must be set, and that will be\n",
    "    used as the column name in the resulting joined DataFrame.\n",
    "对MultiIndex的要求，一个是，另外一个也要是，像是Excel的VLOOKUP函数\n",
    "on : str, list of str, or array-like, optional\n",
    "    Column or index level name(s) in the caller to join on the index\n",
    "    in `other`, otherwise joins index-on-index. If multiple\n",
    "    values given, the `other` DataFrame must have a MultiIndex. Can\n",
    "    pass an array as the join key if it is not already contained in\n",
    "    the calling DataFrame. Like an Excel VLOOKUP operation.\n",
    "四种操作：表述和以前不大一样\n",
    "how : {'left', 'right', 'outer', 'inner'}, default 'left'\n",
    "    How to handle the operation of the two objects.\n",
    "\n",
    "    * left: use calling frame's index (or column if on is specified)\n",
    "    * right: use `other`'s index.\n",
    "    * outer: form union of calling frame's index (or column if on is\n",
    "      specified) with `other`'s index, and sort it.\n",
    "      lexicographically.\n",
    "    * inner: form intersection of calling frame's index (or column if\n",
    "      on is specified) with `other`'s index, preserving the order\n",
    "      of the calling's one.\n",
    "左边表格要加的后缀\n",
    "lsuffix : str, default ''\n",
    "    Suffix to use from left frame's overlapping columns.\n",
    "右边表格要加的后缀\n",
    "rsuffix : str, default ''\n",
    "    Suffix to use from right frame's overlapping columns.\n",
    "合成以后根据字典来排结果\n",
    "sort : bool, default False\n",
    "    Order result DataFrame lexicographically by the join key. If False,\n",
    "    the order of the join key depends on the join type (how keyword).\n",
    "\n",
    "```\n",
    "注意：\n",
    "```\n",
    "在传入很多DF的时候，左右后缀都用不了\n",
    "Notes\n",
    "-----\n",
    "Parameters `on`, `lsuffix`, and `rsuffix` are not supported when\n",
    "passing a list of `DataFrame` objects.\n",
    "```\n",
    "\n",
    "一些例子：\n",
    "```\n",
    "Examples\n",
    "--------\n",
    ">>> df = pd.DataFrame({'key': ['K0', 'K1', 'K2', 'K3', 'K4', 'K5'],\n",
    "...                    'A': ['A0', 'A1', 'A2', 'A3', 'A4', 'A5']})\n",
    "\n",
    ">>> df\n",
    "  key   A\n",
    "0  K0  A0\n",
    "1  K1  A1\n",
    "2  K2  A2\n",
    "3  K3  A3\n",
    "4  K4  A4\n",
    "5  K5  A5\n",
    "\n",
    ">>> other = pd.DataFrame({'key': ['K0', 'K1', 'K2'],\n",
    "...                       'B': ['B0', 'B1', 'B2']})\n",
    "\n",
    ">>> other\n",
    "  key   B\n",
    "0  K0  B0\n",
    "1  K1  B1\n",
    "2  K2  B2\n",
    "\n",
    "Join DataFrames using their indexes.\n",
    "\n",
    ">>> df.join(other, lsuffix='_caller', rsuffix='_other')\n",
    "  key_caller   A key_other    B\n",
    "0         K0  A0        K0   B0\n",
    "1         K1  A1        K1   B1\n",
    "2         K2  A2        K2   B2\n",
    "3         K3  A3       NaN  NaN\n",
    "4         K4  A4       NaN  NaN\n",
    "5         K5  A5       NaN  NaN\n",
    "\n",
    "If we want to join using the key columns, we need to set key to be\n",
    "the index in both `df` and `other`. The joined DataFrame will have\n",
    "key as its index.\n",
    "需要使用key来合并的时候，需要先set_index()\n",
    "\n",
    ">>> df.set_index('key').join(other.set_index('key'))\n",
    "      A    B\n",
    "key\n",
    "K0   A0   B0\n",
    "K1   A1   B1\n",
    "K2   A2   B2\n",
    "K3   A3  NaN\n",
    "K4   A4  NaN\n",
    "K5   A5  NaN\n",
    "另外一种使用key来合并的方式，使用other的茵蒂克丝，这种方式保留了原始的DF的标签\n",
    "Another option to join using the key columns is to use the `on`\n",
    "parameter. DataFrame.join always uses `other`'s index but we can use\n",
    "any column in `df`. This method preserves the original DataFrame's\n",
    "index in the result.\n",
    "\n",
    ">>> df.join(other.set_index('key'), on='key')\n",
    "  key   A    B\n",
    "0  K0  A0   B0\n",
    "1  K1  A1   B1\n",
    "2  K2  A2   B2\n",
    "3  K3  A3  NaN\n",
    "4  K4  A4  NaN\n",
    "5  K5  A5  NaN"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "collapsed": false
   },
   "source": [
    "## 二、方向连接\n",
    "### 1. concat\n",
    "\n",
    "前面介绍了关系型连接，其中最重要的参数是`on`和`how`，但有时候用户并不关心以哪一列为键来合并，只是希望把两个表或者多个表按照纵向或者横向拼接，为这种需求，`pandas`中提供了`concat`函数来实现。\n",
    "\n",
    "在`concat`中，最常用的有三个参数，它们是`axis, join, keys`，分别表示拼接方向，连接形式，以及在新表中指示来自于哪一张旧表的名字。这里需要特别注意，`join`和`keys`与之前提到的`join`函数和键的概念没有任何关系。\n",
    "\n",
    "在默认状态下的`axis=0`，表示纵向拼接多个表，常常用于多个样本的拼接；而`axis=1`表示横向拼接多个表，常用于多个字段或特征的拼接。\n",
    "\n",
    "例如，纵向合并各表中人的信息："
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "        Name  Age\n",
      "0  San Zhang   20\n",
      "1      Si Li   30\n",
      "_____________________________________\n",
      "      Name  Age\n",
      "0  Wu Wang   40\n",
      "_____________________________________\n",
      "        Name  Age\n",
      "0  San Zhang   20\n",
      "1      Si Li   30\n",
      "0    Wu Wang   40\n"
     ]
    }
   ],
   "source": [
    "df1 = pd.DataFrame({'Name':['San Zhang','Si Li'], 'Age':[20,30]})\n",
    "df2 = pd.DataFrame({'Name':['Wu Wang'], 'Age':[40]})\n",
    "print(df1)\n",
    "print('_____________________________________')\n",
    "print(df2)\n",
    "print('_____________________________________')\n",
    "print(pd.concat([df1, df2]))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "collapsed": false
   },
   "source": [
    "横向合并各表中的字段："
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "   Grade\n",
      "0     80\n",
      "1     90\n",
      "_____________________________________\n",
      "  Gender\n",
      "0      M\n",
      "1      F\n",
      "_____________________________________\n",
      "        Name  Age  Grade Gender\n",
      "0  San Zhang   20     80      M\n",
      "1      Si Li   30     90      F\n"
     ]
    }
   ],
   "source": [
    "df2 = pd.DataFrame({'Grade':[80, 90]})\n",
    "df3 = pd.DataFrame({'Gender':['M', 'F']})\n",
    "print(df2)\n",
    "print('_____________________________________')\n",
    "print(df3)\n",
    "\n",
    "print('_____________________________________')\n",
    "print(pd.concat([df1, df2, df3], 1))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "collapsed": false
   },
   "source": [
    "虽然说`concat`是处理关系型合并的函数，但是它仍然是关于索引进行连接的。纵向拼接会根据列索引对其，默认状态下`join=outer`，表示保留所有的列，并将不存在的值设为缺失；`join=inner`，表示保留两个表都出现过的列。横向拼接则根据行索引对齐，`join`参数可以类似设置。"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Name</th>\n",
       "      <th>Age</th>\n",
       "      <th>Gender</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>San Zhang</td>\n",
       "      <td>20.0</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Si Li</td>\n",
       "      <td>30.0</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Wu Wang</td>\n",
       "      <td>NaN</td>\n",
       "      <td>M</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "        Name   Age Gender\n",
       "0  San Zhang  20.0    NaN\n",
       "1      Si Li  30.0    NaN\n",
       "0    Wu Wang   NaN      M"
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df2 = pd.DataFrame({'Name':['Wu Wang'], 'Gender':['M']})\n",
    "pd.concat([df1, df2])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Name</th>\n",
       "      <th>Age</th>\n",
       "      <th>Grade</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>San Zhang</td>\n",
       "      <td>20.0</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Si Li</td>\n",
       "      <td>30.0</td>\n",
       "      <td>80.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>90.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "        Name   Age  Grade\n",
       "0  San Zhang  20.0    NaN\n",
       "1      Si Li  30.0   80.0\n",
       "2        NaN   NaN   90.0"
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df2 = pd.DataFrame({'Grade':[80, 90]}, index=[1, 2])\n",
    "pd.concat([df1, df2], 1)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 47,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Name</th>\n",
       "      <th>Age</th>\n",
       "      <th>Grade</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Si Li</td>\n",
       "      <td>30</td>\n",
       "      <td>80</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "    Name  Age  Grade\n",
       "1  Si Li   30     80"
      ]
     },
     "execution_count": 47,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "pd.concat([df1, df2], axis=1, join='inner')#取交集"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 49,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Name</th>\n",
       "      <th>Age</th>\n",
       "      <th>Grade</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>San Zhang</td>\n",
       "      <td>20.0</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Si Li</td>\n",
       "      <td>30.0</td>\n",
       "      <td>80.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>90.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "        Name   Age  Grade\n",
       "0  San Zhang  20.0    NaN\n",
       "1      Si Li  30.0   80.0\n",
       "2        NaN   NaN   90.0"
      ]
     },
     "execution_count": 49,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "pd.concat([df1, df2], axis=1, join='outer')#取并集"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "collapsed": false
   },
   "source": [
    "因此，当确认要使用多表直接的方向合并时，尤其是横向的合并，可以先用`reset_index`方法恢复默认整数索引再进行合并，防止出现由索引的误对齐和重复索引的笛卡尔积带来的错误结果。\n",
    "\n",
    "最后，`keys`参数的使用场景在于多个表合并后，用户仍然想要知道新表中的数据来自于哪个原表，这时可以通过`keys`参数产生多级索引进行标记。例如，第一个表中都是一班的同学，而第二个表中都是二班的同学，可以使用如下方式合并："
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 50,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th>Name</th>\n",
       "      <th>Age</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th rowspan=\"2\" valign=\"top\">one</th>\n",
       "      <th>0</th>\n",
       "      <td>San Zhang</td>\n",
       "      <td>20</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Si Li</td>\n",
       "      <td>21</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>two</th>\n",
       "      <th>0</th>\n",
       "      <td>Wu Wang</td>\n",
       "      <td>21</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "            Name  Age\n",
       "one 0  San Zhang   20\n",
       "    1      Si Li   21\n",
       "two 0    Wu Wang   21"
      ]
     },
     "execution_count": 50,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df1 = pd.DataFrame({'Name':['San Zhang','Si Li'], 'Age':[20,21]})\n",
    "df2 = pd.DataFrame({'Name':['Wu Wang'],'Age':[21]})\n",
    "pd.concat([df1, df2], keys=['one', 'two'])"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "collapsed": false
   },
   "source": [
    "看看concat中有没有什么遗漏的地方`pd.concat?`\n",
    "\n",
    "首先是输入输出：好多可以选的选项\n",
    "```\n",
    "pd.concat(\n",
    "    objs: Union[Iterable[~FrameOrSeries], Mapping[Union[Hashable, NoneType], ~FrameOrSeries]],\n",
    "    axis=0,\n",
    "    join='outer',\n",
    "    ignore_index: bool = False,\n",
    "    keys=None,\n",
    "    levels=None,\n",
    "    names=None,\n",
    "    verify_integrity: bool = False,\n",
    "    sort: bool = False,\n",
    "    copy: bool = True,\n",
    ") -> Union[ForwardRef('DataFrame'), ForwardRef('Series')]\n",
    "Docstring:\n",
    "Concatenate pandas objects along a particular axis with optional set logic\n",
    "along the other axes.\n",
    "提到了如果不同对象的label是一样的话会方便一些\n",
    "Can also add a layer of hierarchical indexing on the concatenation axis,\n",
    "which may be useful if the labels are the same (or overlapping) on\n",
    "the passed axis number.\n",
    "\n",
    "Returns\n",
    "返回对象是obj，连接的都是Series的时候，返回的就是Series；在DF中存在obj时，返回DF\n",
    "-------\n",
    "object, type of objs\n",
    "    When concatenating all ``Series`` along the index (axis=0), a\n",
    "    ``Series`` is returned. When ``objs`` contains at least one\n",
    "    ``DataFrame``, a ``DataFrame`` is returned. When concatenating along\n",
    "    the columns (axis=1), a ``DataFrame`` is returned.\n",
    "```\n",
    "具体的用法：\n",
    "```\n",
    "Parameters\n",
    "----------\n",
    "一串，或者Series的mapping的Series或者式DF；任何空值都会被丢弃除非都是空的，然后就报错\n",
    "objs : a sequence or mapping of Series or DataFrame objects\n",
    "    If a mapping is passed, the sorted keys will be used as the `keys`\n",
    "    argument, unless it is passed, in which case the values will be\n",
    "    selected (see below). Any None objects will be dropped silently unless\n",
    "    they are all None in which case a ValueError will be raised.\n",
    "0是行，横着的；1是列竖着的\n",
    "axis : {0/'index', 1/'columns'}, default 0\n",
    "    The axis to concatenate along.\n",
    "join : {'inner', 'outer'}, default 'outer'默认取并集\n",
    "    How to handle indexes on other axis (or axes).\n",
    "ignore_index : bool, default False\n",
    "选择的时候，不使用连接的轴上的茵蒂克丝，，直接用0到n-1;但是另外一条轴上的茵蒂克丝还有用\n",
    "    If True, do not use the index values along the concatenation axis. The\n",
    "    resulting axis will be labeled 0, ..., n - 1. This is useful if you are\n",
    "    concatenating objects where the concatenation axis does not have\n",
    "    meaningful indexing information. Note the index values on the other\n",
    "    axes are still respected in the join.\n",
    "   \n",
    "keys : sequence, default None\n",
    "多级索引时，使用这个keys传的健作为最外层索引\n",
    "    If multiple levels passed, should contain tuples. Construct\n",
    "    hierarchical index using the passed keys as the outermost level.\n",
    "levels : list of sequences, default None\n",
    "    Specific levels (unique values) to use for constructing a\n",
    "    MultiIndex. Otherwise they will be inferred from the keys.\n",
    "names : list, default None\n",
    "    Names for the levels in the resulting hierarchical index.\n",
    "verify_integrity : bool, default False\n",
    "检查新的连接出来的轴有没有重复值，这个将会很耗费成本（expensive？）在实际数据连接中\n",
    "    Check whether the new concatenated axis contains duplicates. This can\n",
    "    be very expensive relative to the actual data concatenation.\n",
    "sort : bool, default False\n",
    "把不是用于连接的轴排序，\n",
    "    Sort non-concatenation axis if it is not already aligned when `join`\n",
    "    is 'outer'.\n",
    "    This has no effect when ``join='inner'``, which already preserves\n",
    "    the order of the non-concatenation axis.\n",
    "\n",
    "copy : bool, default True\n",
    "    If False, do not copy data unnecessarily.\n",
    "\n",
    "```\n",
    "补充文档：\n",
    "<https://pandas.pydata.org/pandas-docs/stable/user_guide/merging.html>`__.  \n",
    "一些例子\n",
    "```\n",
    "Examples\n",
    "--------\n",
    "Combine two ``Series``.\n",
    "\n",
    ">>> s1 = pd.Series(['a', 'b'])\n",
    ">>> s2 = pd.Series(['c', 'd'])\n",
    ">>> pd.concat([s1, s2])\n",
    "0    a\n",
    "1    b\n",
    "0    c\n",
    "1    d\n",
    "dtype: object\n",
    "\n",
    "Clear the existing index and reset it in the result\n",
    "by setting the ``ignore_index`` option to ``True``.\n",
    "把原来的茵蒂克丝清除掉\n",
    "\n",
    ">>> pd.concat([s1, s2], ignore_index=True)\n",
    "0    a\n",
    "1    b\n",
    "2    c\n",
    "3    d\n",
    "dtype: object\n",
    "添加keys作为最外层\n",
    "Add a hierarchical index at the outermost level of\n",
    "the data with the ``keys`` option.\n",
    "\n",
    ">>> pd.concat([s1, s2], keys=['s1', 's2'])\n",
    "s1  0    a\n",
    "    1    b\n",
    "s2  0    c\n",
    "    1    d\n",
    "dtype: object\n",
    "给外层命名\n",
    "Label the index keys you create with the ``names`` option.\n",
    "\n",
    ">>> pd.concat([s1, s2], keys=['s1', 's2'],\n",
    "...           names=['Series name', 'Row ID'])\n",
    "Series name  Row ID\n",
    "s1           0         a\n",
    "             1         b\n",
    "s2           0         c\n",
    "             1         d\n",
    "dtype: object\n",
    "\n",
    "Combine two ``DataFrame`` objects with identical columns.\n",
    "\n",
    ">>> df1 = pd.DataFrame([['a', 1], ['b', 2]],\n",
    "...                    columns=['letter', 'number'])\n",
    ">>> df1\n",
    "  letter  number\n",
    "0      a       1\n",
    "1      b       2\n",
    ">>> df2 = pd.DataFrame([['c', 3], ['d', 4]],\n",
    "...                    columns=['letter', 'number'])\n",
    ">>> df2\n",
    "  letter  number\n",
    "0      c       3\n",
    "1      d       4\n",
    ">>> pd.concat([df1, df2])直接合并\n",
    "  letter  number\n",
    "0      a       1\n",
    "1      b       2\n",
    "0      c       3\n",
    "1      d       4\n",
    "\n",
    "Combine ``DataFrame`` objects with overlapping columns\n",
    "and return everything. Columns outside the intersection will\n",
    "be filled with ``NaN`` values.\n",
    "合并上，原来不存在的值就变成了NaN\n",
    "\n",
    ">>> df3 = pd.DataFrame([['c', 3, 'cat'], ['d', 4, 'dog']],\n",
    "...                    columns=['letter', 'number', 'animal'])\n",
    ">>> df3\n",
    "  letter  number animal\n",
    "0      c       3    cat\n",
    "1      d       4    dog\n",
    ">>> pd.concat([df1, df3], sort=False)\n",
    "  letter  number animal\n",
    "0      a       1    NaN\n",
    "1      b       2    NaN\n",
    "0      c       3    cat\n",
    "1      d       4    dog\n",
    "\n",
    "Combine ``DataFrame`` objects with overlapping columns\n",
    "and return only those that are shared by passing ``inner`` to\n",
    "the ``join`` keyword argument.\n",
    "使用inner只返回重复的\n",
    ">>> pd.concat([df1, df3], join=\"inner\")\n",
    "  letter  number\n",
    "0      a       1\n",
    "1      b       2\n",
    "0      c       3\n",
    "1      d       4\n",
    "\n",
    "Combine ``DataFrame`` objects horizontally along the x axis by\n",
    "passing in ``axis=1``.\n",
    "\n",
    ">>> df4 = pd.DataFrame([['bird', 'polly'], ['monkey', 'george']],\n",
    "...                    columns=['animal', 'name'])\n",
    ">>> pd.concat([df1, df4], axis=1)按照列连接\n",
    "  letter  number  animal    name\n",
    "0      a       1    bird   polly\n",
    "1      b       2  monkey  george\n",
    "防止出现重复的元素\n",
    "Prevent the result from including duplicate index values with the\n",
    "``verify_integrity`` option.\n",
    "\n",
    ">>> df5 = pd.DataFrame([1], index=['a'])\n",
    ">>> df5\n",
    "   0\n",
    "a  1\n",
    ">>> df6 = pd.DataFrame([2], index=['a'])\n",
    ">>> df6\n",
    "   0\n",
    "a  2\n",
    ">>> pd.concat([df5, df6], verify_integrity=True)\n",
    "Traceback (most recent call last):\n",
    "    ...\n",
    "ValueError: Indexes have overlapping values: ['a']"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "collapsed": false
   },
   "source": [
    "### 2. 序列与表的合并\n",
    "\n",
    "利用`concat`可以实现多个表之间的方向拼接，如果想要把一个序列追加到表的行末或者列末，则可以分别使用`append`和`assign`方法。\n",
    "\n",
    "在`append`中，如果原表是默认整数序列的索引，那么可以使用`ignore_index=True`对新序列对应索引的自动标号，否则必须对`Series`指定`name`属性。"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Name</th>\n",
       "      <th>Age</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>San Zhang</td>\n",
       "      <td>20</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Si Li</td>\n",
       "      <td>30</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Wu Wang</td>\n",
       "      <td>21</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "        Name  Age\n",
       "0  San Zhang   20\n",
       "1      Si Li   30\n",
       "2    Wu Wang   21"
      ]
     },
     "execution_count": 9,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "s = pd.Series(['Wu Wang', 21], index = df1.columns)\n",
    "df1.append(s, ignore_index=True)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "collapsed": false
   },
   "source": [
    "对于`assign`而言，虽然可以利用其添加新的列，但一般通过`df['new_col'] = ...`的形式就可以等价地添加新列。同时，使用`[]`修改的缺点是它会直接在原表上进行改动，而`assign`返回的是一个临时副本："
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Name</th>\n",
       "      <th>Age</th>\n",
       "      <th>Grade</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>San Zhang</td>\n",
       "      <td>20</td>\n",
       "      <td>80</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Si Li</td>\n",
       "      <td>30</td>\n",
       "      <td>90</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "        Name  Age  Grade\n",
       "0  San Zhang   20     80\n",
       "1      Si Li   30     90"
      ]
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "s = pd.Series([80, 90])\n",
    "df1.assign(Grade=s)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 53,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Name</th>\n",
       "      <th>Age</th>\n",
       "      <th>Grade</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>San Zhang</td>\n",
       "      <td>20</td>\n",
       "      <td>80</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Si Li</td>\n",
       "      <td>21</td>\n",
       "      <td>90</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "        Name  Age  Grade\n",
       "0  San Zhang   20     80\n",
       "1      Si Li   21     90"
      ]
     },
     "execution_count": 53,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df1['Grade'] = s\n",
    "df1"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "collapsed": false
   },
   "source": [
    "## 三、类连接操作\n",
    "\n",
    "除了上述介绍的若干连接函数之外，`pandas`中还设计了一些函数能够对两个表进行某些操作，这里把它们统称为类连接操作。\n",
    "\n",
    "### 1. 比较\n",
    "\n",
    "`compare`是在`1.1.0`后引入的新函数，它能够比较两个表或者序列的不同处并将其汇总展示："
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 55,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "        Name  Age  Class\n",
      "0  San Zhang   20    one\n",
      "1      Si Li   21    two\n",
      "2    Wu Wang   21  three\n",
      "____________________________________\n",
      "        Name  Age  Class\n",
      "0  San Zhang   20    one\n",
      "1      Li Si   21    two\n",
      "2    Wu Wang   21  Three\n",
      "————————————————————————————————————\n",
      "    Name         Class       \n",
      "    self  other   self  other\n",
      "1  Si Li  Li Si    NaN    NaN\n",
      "2    NaN    NaN  three  Three\n"
     ]
    }
   ],
   "source": [
    "df1 = pd.DataFrame({'Name':['San Zhang', 'Si Li', 'Wu Wang'],\n",
    "                    'Age':[20, 21 ,21],\n",
    "                    'Class':['one', 'two', 'three']})\n",
    "df2 = pd.DataFrame({'Name':['San Zhang', 'Li Si', 'Wu Wang'],\n",
    "                    'Age':[20, 21 ,21],\n",
    "                    'Class':['one', 'two', 'Three']})\n",
    "print(df1)\n",
    "print('____________________________________')\n",
    "print(df2)\n",
    "print('————————————————————————————————————')\n",
    "\n",
    "print(df1.compare(df2))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "collapsed": false
   },
   "source": [
    "结果中返回了不同值所在的行列，如果相同则会被填充为缺失值`NaN`，其中`other`和`self`分别指代传入的参数表和被调用的表自身。\n",
    "\n",
    "如果想要完整显示表中所有元素的比较情况，可以设置`keep_shape=True`："
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead tr th {\n",
       "        text-align: left;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr>\n",
       "      <th></th>\n",
       "      <th colspan=\"2\" halign=\"left\">Name</th>\n",
       "      <th colspan=\"2\" halign=\"left\">Age</th>\n",
       "      <th colspan=\"2\" halign=\"left\">Class</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th></th>\n",
       "      <th>self</th>\n",
       "      <th>other</th>\n",
       "      <th>self</th>\n",
       "      <th>other</th>\n",
       "      <th>self</th>\n",
       "      <th>other</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Si Li</td>\n",
       "      <td>Li Si</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>three</td>\n",
       "      <td>Three</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "    Name         Age        Class       \n",
       "    self  other self other   self  other\n",
       "0    NaN    NaN  NaN   NaN    NaN    NaN\n",
       "1  Si Li  Li Si  NaN   NaN    NaN    NaN\n",
       "2    NaN    NaN  NaN   NaN  three  Three"
      ]
     },
     "execution_count": null,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df1.compare(df2, keep_shape=True)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "collapsed": false
   },
   "source": [
    "### 2. 组合\n",
    "\n",
    "`combine`函数能够让两张表按照一定的规则进行组合，在进行规则比较时会自动进行列索引的对齐。对于传入的函数而言，每一次操作中输入的参数是来自两个表的同名`Series`，依次传入的列是两个表列名的并集，例如下面这个例子会依次传入`A,B,C,D`四组序列，每组为左右表的两个序列。同时，进行`A`列比较的时候，`s1`指代的就是一个全空的序列，因为它在被调用的表中并不存在，并且来自第一个表的序列索引会被`reindex`成两个索引的并集。具体的过程可以通过在传入的函数中插入适当的`print`方法查看。\n",
    "\n",
    "下面的例子表示选出对应索引位置较小的元素："
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>A</th>\n",
       "      <th>B</th>\n",
       "      <th>C</th>\n",
       "      <th>D</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>NaN</td>\n",
       "      <td>4.0</td>\n",
       "      <td>6.0</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "    A    B    C   D\n",
       "0 NaN  NaN  NaN NaN\n",
       "1 NaN  4.0  6.0 NaN\n",
       "2 NaN  NaN  NaN NaN"
      ]
     },
     "execution_count": 20,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "def choose_min(s1, s2):\n",
    "    s2 = s2.reindex_like(s1)\n",
    "    res = s1.where(s1<s2, s2)#where是选出s1s2中较小的填进去\n",
    "    res = res.mask(s1.isna()) # isna表示是否为缺失值，返回布尔序列\n",
    "    return res\n",
    "df1 = pd.DataFrame({'A':[1,2], 'B':[3,4], 'C':[5,6]})\n",
    "df2 = pd.DataFrame({'B':[5,6], 'C':[7,8], 'D':[9,10]}, index=[1,2])\n",
    "df1.combine(df2, choose_min)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "collapsed": false
   },
   "source": [
    "#### 【练一练】\n",
    "请在上述代码的基础上修改，保留`df2`中4个未被`df1`替换的相应位置原始值。\n",
    "\n",
    "首先，原来的函数输出的是`df1`和`df2`的重复值中较小的df1的内容\n",
    "\n",
    "需要把函数改一改，原本需要找的缺失值是s1，改成s2；又尝试一下overwrite选项，无用"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>A</th>\n",
       "      <th>B</th>\n",
       "      <th>C</th>\n",
       "      <th>D</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>NaN</td>\n",
       "      <td>4.0</td>\n",
       "      <td>6.0</td>\n",
       "      <td>9.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>NaN</td>\n",
       "      <td>6.0</td>\n",
       "      <td>8.0</td>\n",
       "      <td>10.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "    A    B    C     D\n",
       "0 NaN  NaN  NaN   NaN\n",
       "1 NaN  4.0  6.0   9.0\n",
       "2 NaN  6.0  8.0  10.0"
      ]
     },
     "execution_count": 24,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#print(df1)\r\n",
    "#print(df2)\r\n",
    "def choose_min(s1, s2):\r\n",
    "    s2 = s2.reindex_like(s1)\r\n",
    "    res = s1.where(s1<s2, s2)#where是选出s1s2中较小的填进去\r\n",
    "    res = res.mask(s2.isna()) # isna表示是否为缺失值，返回布尔序列\r\n",
    "    return res\r\n",
    "df1.combine(df2, choose_min,overwrite=True)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "collapsed": false
   },
   "source": [
    "\n",
    "#### 【END】\n",
    "此外，设置`overwrite`参数为`False`可以保留$\\color{red}{被调用表}$中未出现在传入的参数表中的列，而不会设置未缺失值："
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>A</th>\n",
       "      <th>B</th>\n",
       "      <th>C</th>\n",
       "      <th>D</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2.0</td>\n",
       "      <td>4.0</td>\n",
       "      <td>6.0</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "     A    B    C   D\n",
       "0  1.0  NaN  NaN NaN\n",
       "1  2.0  4.0  6.0 NaN\n",
       "2  NaN  NaN  NaN NaN"
      ]
     },
     "execution_count": null,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df1.combine(df2, choose_min, overwrite=False)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "collapsed": false
   },
   "source": [
    "#### 【练一练】\n",
    "除了`combine`之外，`pandas`中还有一个`combine_first`方法，其功能是在对两张表组合时，若第二张表中的值在第一张表中对应索引位置的值不是缺失状态，那么就使用第一张表的值填充。下面给出一个例子，请用`combine`函数完成相同的功能。\n",
    "\n",
    "定义一个function，在某位置上s1.isna为真，则输入s2,；为假，则输入s1，并完成两张表的合并\n",
    "\n",
    "尝试了lamdba函数，未果，改尝试普通的函数，成功\n",
    "#### 【END】"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 31,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "   A    B\n",
      "0  1  3.0\n",
      "1  2  NaN\n",
      "_______________\n",
      "   A  B\n",
      "1  5  7\n",
      "2  6  8\n",
      "_______________\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>A</th>\n",
       "      <th>B</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1.0</td>\n",
       "      <td>3.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2.0</td>\n",
       "      <td>7.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>6.0</td>\n",
       "      <td>8.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "     A    B\n",
       "0  1.0  3.0\n",
       "1  2.0  7.0\n",
       "2  6.0  8.0"
      ]
     },
     "execution_count": 31,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df1 = pd.DataFrame({'A':[1,2], 'B':[3,np.nan]})\n",
    "df2 = pd.DataFrame({'A':[5,6], 'B':[7,8]}, index=[1,2])\n",
    "print(df1)\n",
    "print('_______________')\n",
    "print(df2)\n",
    "print('_______________')\n",
    "df1.combine_first(df2)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 40,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>A</th>\n",
       "      <th>B</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>False</td>\n",
       "      <td>True</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "       A      B\n",
       "0  False  False\n",
       "1  False   True"
      ]
     },
     "execution_count": 40,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df1.isna()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 52,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>A</th>\n",
       "      <th>B</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1.0</td>\n",
       "      <td>3.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2.0</td>\n",
       "      <td>7.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>6.0</td>\n",
       "      <td>8.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "     A    B\n",
       "0  1.0  3.0\n",
       "1  2.0  7.0\n",
       "2  6.0  8.0"
      ]
     },
     "execution_count": 52,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "'''\r\n",
    "lambda函数没有成功\r\n",
    "cf = lambda s1,s2:s1 if s1.isna()==True else s2\r\n",
    "df1.combine(df2,cf)\r\n",
    "'''\r\n",
    "def cf(s1, s2):\r\n",
    "    s2 = s2.reindex_like(s1)\r\n",
    "    res = s1.where(s1.isna()==False, s2)\r\n",
    "    return res\r\n",
    "df1.combine(df2,cf)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "collapsed": false
   },
   "source": [
    "## 四、练习\n",
    "### Ex1：美国疫情数据集\n",
    "\n",
    "现有美国4月12日至11月16日的疫情报表，请将`New York`的`Confirmed, Deaths, Recovered, Active`合并为一张表，索引为按如下方法生成的日期字符串序列：\n",
    "\n",
    "首先看一下数据长啥样，然后决定把所有数据读进一个大的df里面慢慢筛选\n",
    "\n",
    "一开始没看到data可以用，傻乎乎又写了一个读数据的,参考：https://blog.csdn.net/qq_41232071/article/details/80472546?utm_source=blogxgwz1\n",
    "\n",
    "然而总是有错，抄答案吧\n",
    "\n",
    "使用for读入data中的时间范围，然后每次往已有的list里写，读的时候就先进行州和四项参数的晒选"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 73,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Province_State</th>\n",
       "      <th>Country_Region</th>\n",
       "      <th>Last_Update</th>\n",
       "      <th>Lat</th>\n",
       "      <th>Long_</th>\n",
       "      <th>Confirmed</th>\n",
       "      <th>Deaths</th>\n",
       "      <th>Recovered</th>\n",
       "      <th>Active</th>\n",
       "      <th>FIPS</th>\n",
       "      <th>Incident_Rate</th>\n",
       "      <th>People_Tested</th>\n",
       "      <th>People_Hospitalized</th>\n",
       "      <th>Mortality_Rate</th>\n",
       "      <th>UID</th>\n",
       "      <th>ISO3</th>\n",
       "      <th>Testing_Rate</th>\n",
       "      <th>Hospitalization_Rate</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Alabama</td>\n",
       "      <td>US</td>\n",
       "      <td>2020-08-11 04:35:27</td>\n",
       "      <td>32.3182</td>\n",
       "      <td>-86.9023</td>\n",
       "      <td>103020</td>\n",
       "      <td>1797</td>\n",
       "      <td>37923.0</td>\n",
       "      <td>63300.0</td>\n",
       "      <td>1</td>\n",
       "      <td>2101.083275</td>\n",
       "      <td>780567.0</td>\n",
       "      <td>12070.0</td>\n",
       "      <td>1.744321</td>\n",
       "      <td>84000001</td>\n",
       "      <td>USA</td>\n",
       "      <td>15919.591041</td>\n",
       "      <td>11.716172</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Alaska</td>\n",
       "      <td>US</td>\n",
       "      <td>2020-08-11 04:35:27</td>\n",
       "      <td>61.3707</td>\n",
       "      <td>-152.4044</td>\n",
       "      <td>3774</td>\n",
       "      <td>26</td>\n",
       "      <td>1332.0</td>\n",
       "      <td>2416.0</td>\n",
       "      <td>2</td>\n",
       "      <td>515.894443</td>\n",
       "      <td>280343.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.688924</td>\n",
       "      <td>84000002</td>\n",
       "      <td>USA</td>\n",
       "      <td>38322.044440</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>American Samoa</td>\n",
       "      <td>US</td>\n",
       "      <td>2020-08-11 04:35:27</td>\n",
       "      <td>-14.2710</td>\n",
       "      <td>-170.1320</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.0</td>\n",
       "      <td>60</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>1396.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>16</td>\n",
       "      <td>ASM</td>\n",
       "      <td>2508.941248</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   Province_State Country_Region          Last_Update      Lat     Long_  \\\n",
       "0         Alabama             US  2020-08-11 04:35:27  32.3182  -86.9023   \n",
       "1          Alaska             US  2020-08-11 04:35:27  61.3707 -152.4044   \n",
       "2  American Samoa             US  2020-08-11 04:35:27 -14.2710 -170.1320   \n",
       "\n",
       "   Confirmed  Deaths  Recovered   Active  FIPS  Incident_Rate  People_Tested  \\\n",
       "0     103020    1797    37923.0  63300.0     1    2101.083275       780567.0   \n",
       "1       3774      26     1332.0   2416.0     2     515.894443       280343.0   \n",
       "2          0       0        NaN      0.0    60       0.000000         1396.0   \n",
       "\n",
       "   People_Hospitalized  Mortality_Rate       UID ISO3  Testing_Rate  \\\n",
       "0              12070.0        1.744321  84000001  USA  15919.591041   \n",
       "1                  NaN        0.688924  84000002  USA  38322.044440   \n",
       "2                  NaN             NaN        16  ASM   2508.941248   \n",
       "\n",
       "   Hospitalization_Rate  \n",
       "0             11.716172  \n",
       "1                   NaN  \n",
       "2                   NaN  "
      ]
     },
     "execution_count": 73,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "数据长啥样\r\n",
    "df=pd.read_csv('data/us_report/08-10-2020.csv')\r\n",
    "df.head(3)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 55,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "['04-12-2020', '04-13-2020', '04-14-2020', '04-15-2020', '04-16-2020']"
      ]
     },
     "execution_count": 55,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "date = pd.date_range('20200412', '20201116').to_series()\n",
    "date = date.dt.month.astype('string').str.zfill(2) +'-'+ date.dt.day.astype('string').str.zfill(2) +'-'+ '2020'\n",
    "date = date.tolist()\n",
    "date[:5]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 67,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "'''\r\n",
    "import os\r\n",
    "import re\r\n",
    "data_list = []\r\n",
    "path0 = 'data/us_report/'\r\n",
    "files = os.listdir(path)\r\n",
    "files_csv = list(filter(lambda x: x[-4:]=='.csv' , files))\r\n",
    "#path = os.path.join(path0,file_csv)\r\n",
    "for file in  files_csv:\r\n",
    "    tmp = pd.read_csv(path + file)\r\n",
    "    data_list.append(tmp)\r\n",
    "'''\r\n",
    "#参考答案\r\n",
    "data_list = []\r\n",
    "for d in date:\r\n",
    "    df = pd.read_csv('data/us_report/' + d + '.csv', index_col='Province_State')\r\n",
    "    data = df.loc['New York', ['Confirmed','Deaths','Recovered','Active']]\r\n",
    "    data_list.append(data.to_frame().T)\r\n",
    "res = pd.concat(data_list)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "collapsed": false
   },
   "source": [
    "### Ex2：实现join函数\n",
    "\n",
    "请实现带有`how`参数的`join`函数\n",
    "\n",
    "* 假设连接的两表无公共列\n",
    "* 调用方式为 `join(df1, df2, how=\"left\")`\n",
    "* 给出测试样例\n",
    "\n",
    "不会again，看看答案"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 68,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "\r\n",
    "def join(df1, df2, how='left'):\r\n",
    "    res_col = df1.columns.tolist() +  df2.columns.tolist()#df1和df2的列索引\r\n",
    "    dup = df1.index.unique().intersection(df2.index.unique())#查找相同元素\r\n",
    "    res_df = pd.DataFrame(columns = res_col)\r\n",
    "    for label in dup:\r\n",
    "        cartesian = [list(i)+list(j) for i in df1.loc[label].values for j in df2.loc[label].values]#取出label\r\n",
    "        dup_df = pd.DataFrame(cartesian, index = [label]*len(cartesian), columns = res_col)#建一个DF，index是与label的list长度相当的label\r\n",
    "        res_df = pd.concat([res_df,dup_df])#上面的全拼起来\r\n",
    "    if how in ['left', 'outer']:\r\n",
    "        for label in df1.index.unique().difference(dup):\r\n",
    "            if isinstance(df1.loc[label], pd.DataFrame):#取df1的label\r\n",
    "                cat = [list(i)+[np.nan]*df2.shape[1] for i in df1.loc[label].values]#填充df2的列数的NaN\r\n",
    "            else:\r\n",
    "                cat = [list(i)+[np.nan]*df2.shape[1] for i in df1.loc[label].to_frame().values]\r\n",
    "            dup_df = pd.DataFrame(cat, index = [label]*len(cat), columns = res_col)\r\n",
    "            res_df = pd.concat([res_df,dup_df])\r\n",
    "    if how in ['right', 'outer']:\r\n",
    "        for label in df2.index.unique().difference(dup):取df2的label\r\n",
    "            if isinstance(df2.loc[label], pd.DataFrame):\r\n",
    "                cat = [[np.nan]+list(i)*df1.shape[1] for i in df2.loc[label].values]\r\n",
    "            else:\r\n",
    "                cat = [[np.nan]+list(i)*df1.shape[1] for i in df2.loc[label].to_frame().values]\r\n",
    "            dup_df = pd.DataFrame(cat, index = [label]*len(cat), columns = res_col)#合成df\r\n",
    "            res_df = pd.concat([res_df,dup_df])\r\n",
    "    return res_df   "
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "PaddlePaddle 2.0.0b0 (Python 3.5)",
   "language": "python",
   "name": "py35-paddle1.2.0"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.7.4"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 1
}
